﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using System.Xml;
using System.IO;
using System.Text;
using System.Web;

namespace AppEntity
{
    /// <summary>
    ///AppBuilder 的摘要说明
    /// </summary>
    public static partial class AppBuilderPost
    {
        private class Column
        {
            public string ColID;
            public string Name;
            public string Size;
            public string Type;
            public string BeNull;
            public string DefaultValue;
        }

        public static Dictionary<string, Action<HttpContext, string>> Init()
        {
            Dictionary<string, Action<HttpContext, string>> PostDict = new Dictionary<string, Action<HttpContext, string>>();
            PostDict.Add("buildapp", BuildApp);
            PostDict.Add("buildentity", BuildEntity);
            PostDict.Add("buildcategory", BuildEntity); //保存分类的方法与保存Entity一致，方法内部依靠传入的Action判别。
            PostDict.Add("buildindexedit", BuildIndex);
            return PostDict;
        }

        private static void BuildIndex(HttpContext context, string Action)
        {
            string IndexName = context.Request.Form["indexname"].Trim().ReplaceRegExp(@"\s","");
            string IndexNameOld = context.Request.Form["indexoldname"].Trim().ReplaceRegExp(@"\s", "");
            string IndexTable = context.Request.Form["indextable"].Trim();
            string[] IndexColumns = context.Request.Form.GetValues("indexcol");
            string App = context.Request.QueryString["app"].Trim().ReplaceRegExp("[^0-9]","");

            if (string.IsNullOrEmpty(App) || string.IsNullOrEmpty(IndexName) || string.IsNullOrEmpty(IndexTable) || IndexColumns == null || IndexColumns.Length == 0) return;
            //DROP INDEX [IF EXISTS] [database-name .] index-name
            Common.DataBase.Sqlite Sqlite = new Common.DataBase.Sqlite(Base.GetDataBaseFilePath(App));
            Sqlite.ExecuteSql("DROP INDEX IF EXISTS [{0}]".FormatString(IndexName));
            Sqlite.ExecuteSql("DROP INDEX IF EXISTS [{0}]".FormatString(IndexNameOld));     
            Sqlite.ExecuteSql("CREATE INDEX IF NOT EXISTS [{0}] ON [{1}] ({2})".FormatString(IndexName, IndexTable, "[" + IndexColumns.Join("],[") + "]"));
            Sqlite.Close();
            context.Response.Redirect("?app={0}&action=buildindex".FormatString(App));
            //context.Response.Redirect("?app={0}&table={1}&index={2}&action=buildindexedit".FormatString(App, IndexTable, IndexName));
        }

        private static void BuildApp(HttpContext context, string Action)
        {
            string Name = context.Request.Form["name"].Trim();
            string Description = context.Request.Form["description"].Trim();
            string ID = context.Request.Form["id"];
            if (string.IsNullOrEmpty(ID))
            {
                #region 创建新 APP
                ID = Base.GetMaxID(1)[0];
                string AppSettingFile = Base.GetSettingsPath(ID);
                string AppFolder = AppSettingFile.Substring(0, AppSettingFile.LastIndexOf("\\"));
                AppEntity.Common.Utility.CreateDirectoryEx(AppFolder);
                string NowTime = AppEntity.Common.Utility.GetNowTime();
                XDocument xdom = XDocument.Load(new StringReader("<?xml version='1.0' encoding='utf-8'?><root/>"));
                xdom.Element("root").Add(
                    new XElement("app",
                        new XElement("id", ID),
                        new XElement("name", Name),
                        new XElement("description", Description),
                        new XElement("entities"),
                        new XElement("datecreated", NowTime),
                        new XElement("datemodified")
                    ));
                xdom.Save(AppSettingFile);
                if (!File.Exists(Base.GetSettingsPath() + "index.xml"))
                {
                    File.WriteAllText(Base.GetSettingsPath() + "index.xml", "<?xml version='1.0' encoding='utf-8'?><root/>", Encoding.UTF8);
                }
                XDocument index = Base.GetAppIndex();
                index.Element("root").Add(
                    new XElement("app",
                        new XElement("id", ID),
                        new XElement("name", Name),
                        new XElement("datecreated", NowTime),
                        new XElement("datemodified")
                    ));
                index.Save(Base.GetSettingsPath() + "index.xml");
                #endregion
            }
            else
            {
                #region 修改 App 信息
                string AppSettingFile = Base.GetSettingsPath(ID);
                XDocument xdom = XDocument.Load(AppSettingFile);
                XElement AppElem = xdom.SelectSingleNode("/root/app[id='" + ID + "']");
                string NowTime = AppEntity.Common.Utility.GetNowTime();
                if (AppElem != null)
                {
                    AppElem.Element("name").Value = Name;
                    AppElem.Element("description").Value = Description;
                    AppElem.Element("datemodified").Value = NowTime;
                    xdom.Save(AppSettingFile);
                }
                XDocument index = Base.GetAppIndex();
                XElement AppNode = index.SelectSingleNode("/root/app[id='" + ID + "']");
                AppNode.Element("name").Value = Name;
                AppNode.Element("datemodified").Value = NowTime;
                index.Save(Base.GetSettingsPath() + "index.xml");
                #endregion
            }
            context.Response.Redirect("/appbuilder/?app=" + ID + "&action=buildapp&status=1");
        }

        private static void BuildEntity(HttpContext context, string Action)
        {
            bool IsSavingCategory = Action == "buildcategory";

            string Name = context.Request.Form["name"].Trim();
            string NameZhCn = context.Request.Form["namezh-cn"].Trim();            
            string Description = context.Request.Form["description"].Trim();

            string App = context.Request.Form["app"];
            string Entity = context.Request.Form["entity"];

            if (string.IsNullOrEmpty(App)) return;
            
            string EntitySettingFile = Base.GetSettingsPath(App,Entity);
            //string AppFolder = EntitySettingFile.Substring(0, EntitySettingFile.LastIndexOf("\\")+1);
            XDocument xdom;
            //原来的所有字段，用于修改时数据库，对比字段的变化
            XElement[] ColumnsOld = null;

            #region 保存Entity基本信息
            if (string.IsNullOrEmpty(Entity))
            {
                #region 新增 Entity
                Entity = Base.GetMaxID(1)[0];
                xdom = GetEmptyEntitySettings(App, Entity);
                XElement EntityElem = xdom.SelectSingleNode("/root/app/entity");
                EntityElem.SetAttribute("name", Entity);
                EntityElem.SetAttribute("title", Name);
                EntityElem.SetAttribute("zh-cn", NameZhCn);
                EntityElem.SetAttribute("en-us", Name);
                EntityElem.SelectSingleNode("description").Value = Description;
                xdom.SelectSingleNode("/root/app/entity/datecreated").Value = AppEntity.Common.Utility.GetNowTime();
                //xdom.Save(EntitySettingFile);
                EntitySettingFile = Base.GetSettingsPath(App, Entity);
                #endregion
            }
            else
            {
                #region 修改 Entity
                xdom = XDocument.Load(EntitySettingFile);
                ColumnsOld = xdom.SelectNodes("/root/app/entity/" + (IsSavingCategory ? "category" : "columns") + "/column");
                XElement EntityElem = xdom.SelectSingleNode("/root/app/entity");                
                EntityElem.SetAttribute("title", Name);
                EntityElem.SetAttribute("zh-cn", NameZhCn);
                EntityElem.SetAttribute("en-us", Name);
                EntityElem.SelectSingleNode("description").Value = Description;
                //xdom.Save(EntitySettingFile);
                #endregion
            }
            #endregion

            #region 获取表单值
            string[] ColIDs = context.Request.Form.GetValues("colid");
            string[] ColNames = context.Request.Form.GetValues("colname");
            string[] ColTypes = context.Request.Form.GetValues("coltype");
            string[] ColSizes = context.Request.Form.GetValues("colsize");
            string[] ColDefaultValues = context.Request.Form.GetValues("coldefault");
            string[] ColBeNulls = context.Request.Form.GetValues("colbenull");
            string[] ColControls = context.Request.Form.GetValues("colcontrol");
            string[] ColNameZhCns = context.Request.Form.GetValues("colnamezhcn");
            string[] ColNameEnUss = context.Request.Form.GetValues("colnameenus");
            string[] ColLists = context.Request.Form.GetValues("collist");
            string[] ColAdd = context.Request.Form.GetValues("coladd");
            string[] ColModify = context.Request.Form.GetValues("colmodify");
            string[] ColView = context.Request.Form.GetValues("colview");

            string[] _ColIDs = context.Request.Form.GetValues("_colid");
            string[] _ColNames = context.Request.Form.GetValues("_colname");
            string[] _ColTypes = context.Request.Form.GetValues("_coltype");
            string[] _ColSizes = context.Request.Form.GetValues("_colsize");
            string[] _ColDefaultValues = context.Request.Form.GetValues("_coldefault");
            string[] _ColBeNulls = context.Request.Form.GetValues("_colbenull");
            string[] _ColControls = context.Request.Form.GetValues("_colcontrol");
            string[] _ColNameZhCns = context.Request.Form.GetValues("_colnamezhcn");
            string[] _ColNameEnUss = context.Request.Form.GetValues("_colnameenus");
            string[] _ColLists = context.Request.Form.GetValues("_collist");
            string[] _ColAdd = context.Request.Form.GetValues("_coladd");
            string[] _ColModify = context.Request.Form.GetValues("_colmodify");
            string[] _ColView = context.Request.Form.GetValues("_colview");
            #endregion

            #region 设置字段
            if (!ColNames.Contains("datecreated"))
            {
                throw new NullReferenceException("必须包含 datecreated 字段，否则所有自定义字段的插入位置都无从参考！");
            }
            XElement Columns;
            if (IsSavingCategory)
            {
                Columns = xdom.Element("root").Element("app").Element("entity").Element("category");
            }
            else
            {
                Columns = xdom.Element("root").Element("app").Element("entity").Element("columns");                
            }
            Columns.Elements("column").Remove();
            for (int i = 0; i < ColNames.Length; i++)
            {
                string ColName = ColNames[i].ReplaceRegExp("[^0-9a-z_]", "");
                if (string.IsNullOrEmpty(ColName)) continue;
                #region 插入自定义字段
                if (ColName.ToLower() == "datecreated" && _ColNames != null)
                {
                    for (int j = 0; j < _ColNames.Length; j++)
                    {
                        string _ColName = _ColNames[j].ReplaceRegExp("[^0-9a-z_]", "");
                        if (string.IsNullOrEmpty(_ColName)) continue;
                        XElement _Col = new XElement("column");
                        _Col.SetAttribute("colid", _ColIDs[j]);
                        _Col.SetAttribute("name", _ColName.ToLower());
                        _Col.SetAttribute("title", (string.IsNullOrEmpty(_ColNameEnUss[j]) ? _ColName : _ColNameEnUss[j]).Trim());
                        _Col.SetAttribute("control", _ColControls[j].Trim());
                        _Col.SetAttribute("type", _ColTypes[j].Trim());
                        _Col.SetAttribute("size", _ColSizes[j].Trim());
                        _Col.SetAttribute("default", _ColDefaultValues[j].Trim());
                        _Col.SetAttribute("null", _ColBeNulls[j].Trim());
                        _Col.SetAttribute("en-us", (string.IsNullOrEmpty(_ColNameEnUss[j]) ? _ColName : _ColNameEnUss[j]).Trim());
                        _Col.SetAttribute("zh-cn", (string.IsNullOrEmpty(_ColNameZhCns[j]) ? _ColName : _ColNameZhCns[j]).Trim());
                        Columns.Add(_Col);
                    }
                }
                #endregion
                XElement Col = new XElement("column");
                Col.SetAttribute("colid", ColIDs[i]);
                Col.SetAttribute("name", ColName.ToLower());
                Col.SetAttribute("title", (string.IsNullOrEmpty(ColNameEnUss[i]) ? ColName : ColNameEnUss[i]).Trim());
                Col.SetAttribute("control", ColControls[i].Trim());
                Col.SetAttribute("type", ColTypes[i].Trim());
                Col.SetAttribute("size", ColSizes[i].Trim());
                Col.SetAttribute("default", ColDefaultValues[i].Trim());
                Col.SetAttribute("null", ColBeNulls[i].Trim());
                Col.SetAttribute("en-us", (string.IsNullOrEmpty(ColNameEnUss[i]) ? ColName : ColNameEnUss[i]).Trim());
                Col.SetAttribute("zh-cn", (string.IsNullOrEmpty(ColNameZhCns[i]) ? ColName : ColNameZhCns[i]).Trim());
                Columns.Add(Col);
            }
            #endregion

            #region 按顺序设置页面显示字段
            if (IsSavingCategory == false)
            {
                xdom.SelectNodes("/root/app/entity/pages/page").Remove();
                xdom.SelectSingleNode("/root/app/entity/pages").Add(new XElement("page", new XAttribute("name", "list")));
                xdom.SelectSingleNode("/root/app/entity/pages").Add(new XElement("page", new XAttribute("name", "add")));
                xdom.SelectSingleNode("/root/app/entity/pages").Add(new XElement("page", new XAttribute("name", "modify")));
                xdom.SelectSingleNode("/root/app/entity/pages").Add(new XElement("page", new XAttribute("name", "view")));

                for (int i = 0; i < ColNames.Length; i++)
                {
                    string ColName = ColNames[i].ReplaceRegExp("[^0-9a-z_]", "");
                    if (string.IsNullOrEmpty(ColName)) continue;
                    if (ColName == "datecreated" && _ColNames != null)
                    {
                        for (int j = 0; j < _ColNames.Length; j++)
                        {
                            string _ColName = _ColNames[j].ReplaceRegExp("[^0-9a-z_]", "");
                            if (string.IsNullOrEmpty(_ColName)) continue;
                            if (_ColLists[j] == "1")
                            {
                                xdom.SelectSingleNode("/root/app/entity/pages/page[@name='list']").Add(new XElement("column", new XAttribute("name", _ColName)));
                            }
                            if (_ColAdd[j] == "1")
                            {
                                xdom.SelectSingleNode("/root/app/entity/pages/page[@name='add']").Add(new XElement("column", new XAttribute("name", _ColName)));
                            }
                            if (_ColModify[j] == "1")
                            {
                                xdom.SelectSingleNode("/root/app/entity/pages/page[@name='modify']").Add(new XElement("column", new XAttribute("name", _ColName)));
                            }
                            if (_ColView[j] == "1")
                            {
                                xdom.SelectSingleNode("/root/app/entity/pages/page[@name='view']").Add(new XElement("column", new XAttribute("name", _ColName)));
                            }
                        }
                    }
                    if (ColLists[i] == "1")
                    {
                        xdom.SelectSingleNode("/root/app/entity/pages/page[@name='list']").Add(new XElement("column", new XAttribute("name", ColName)));
                    }
                    if (ColAdd[i] == "1")
                    {
                        xdom.SelectSingleNode("/root/app/entity/pages/page[@name='add']").Add(new XElement("column", new XAttribute("name", ColName)));
                    }
                    if (ColModify[i] == "1")
                    {
                        xdom.SelectSingleNode("/root/app/entity/pages/page[@name='modify']").Add(new XElement("column", new XAttribute("name", ColName)));
                    }
                    if (ColView[i] == "1")
                    {
                        xdom.SelectSingleNode("/root/app/entity/pages/page[@name='view']").Add(new XElement("column", new XAttribute("name", ColName)));
                    }
                }
            }
            #endregion            
                                   
            xdom.Save(EntitySettingFile);

            #region 修改 App 的 setting xml
            XmlDocument appdom = Base.GetAppSetting(App);
            XmlNode Entities = appdom.SelectSingleNode("/root/app[id='"+App+"']/entities");
            
            XmlElement EntityNode = appdom.CreateElement("entity");
            EntityNode.SetAttribute("name", Entity);
            EntityNode.SetAttribute("title", Name);
            EntityNode.SetAttribute("zh-cn", NameZhCn);
            EntityNode.SetAttribute("en-us", Name);
            EntityNode.SetAttribute("datecreated", AppEntity.Common.Utility.GetNowTime());

            Entities.AppendChild(EntityNode);
            appdom.SaveToFile(Base.GetSettingsPath(App));
            #endregion

            #region 创建或修改数据库表等
            string TableName = "Entity-" + Entity + "-" + (IsSavingCategory ? "Category" : "Data");
            InitDataBaseTable(App, Entity, TableName, ColumnsOld, ColIDs.Concat(_ColIDs).ToArray(), ColNames.Concat(_ColNames).ToArray(), ColTypes.Concat(_ColTypes).ToArray(), ColSizes.Concat(_ColSizes).ToArray(), ColBeNulls.Concat(_ColBeNulls).ToArray(), ColDefaultValues.Concat(_ColDefaultValues).ToArray());            
            #endregion

            context.Response.Redirect("/appbuilder/?app=" + App + "&entity="+Entity+"&action="+Action+"&status=1");
        }

        /// <summary>
        /// 创建表库、表
        /// </summary>
        /// <param name="ColName"></param>
        /// <param name="ColType"></param>
        /// <param name="ColSize"></param>
        /// <param name="ColBeNull"></param>
        /// <param name="ColDefaultValue"></param>
        private static void InitDataBaseTable(string App, string Entity, string TableName, XElement[] ColumnsOld, string[] ColIDs, string[] ColName, string[] ColType, string[] ColSize, string[] ColBeNull, string[] ColDefaultValue)
        {
            
            string SqlFile = Base.GetDataBaseFilePath(App);            
            if (!File.Exists(SqlFile))
            {
                AppEntity.Common.DataBase.Sqlite.CreateSqlFile(SqlFile);
                CreateDataTable(App, Entity,TableName, ColName, ColType, ColSize, ColBeNull, ColDefaultValue);
                return;
            }
            
            AppEntity.Common.DataBase.Sqlite Sqlite = new Common.DataBase.Sqlite(SqlFile);
            int ExistsTable = Sqlite.ExecuteScalar("SELECT count(*) FROM sqlite_master WHERE type='table' and tbl_name='" + TableName + "'").ToString().ToInt32(0);
            Sqlite.Close();
            if (ExistsTable == 0)
            {                
                CreateDataTable(App, Entity,TableName, ColName, ColType, ColSize, ColBeNull, ColDefaultValue);
            }
            else
            {               
                AlterDataTable(App, TableName, ColumnsOld, ColIDs, ColName, ColType, ColSize, ColBeNull, ColDefaultValue);
            }                
        }

        /// <summary>
        /// 创建表
        /// </summary>
        private static void CreateDataTable(string App, string Entity, string TableName, string[] ColName, string[] ColType, string[] ColSize, string[] ColBeNull, string[] ColDefaultValue)
        {
            string SqlFile = Base.GetDataBaseFilePath(App);
            AppEntity.Common.DataBase.Sqlite Sqlite = new Common.DataBase.Sqlite(SqlFile);

            string Sql = ConvertXmlSettingToSql("[" + TableName + "]", ColName, ColType, ColSize, ColBeNull, ColDefaultValue);
            Sqlite.ExecuteSql(Sql);
            

            #region 主键 id 从 100000 开始计算
            Dictionary<string, string> NotNull = GetNotNullColumns(ColName, ColBeNull);
            Sqlite.ExecuteScalar("INSERT INTO [" + TableName + "] (id," + NotNull.Keys.ToArray().Join(",") + ") VALUES (100000," + NotNull.Values.ToArray().Join(",") + "); select last_insert_rowid()");
            Sqlite.ExecuteSql("DELETE FROM [" + TableName + "] WHERE id=100000");
            #endregion

            if (TableName.ToLower().Contains("category") == false)
            {
                #region 创建Category表
                CreateTableCategory(App, Entity);
                #endregion

                #region EntitySetting.xml中增加 Category 节点
                string EntitySettingFile = Base.GetSettingsPath(App, Entity);
                XDocument xdom = XDocument.Load(EntitySettingFile);
                if (xdom.SelectSingleNode("/root/app/entity/category") == null)
                {
                    xdom.SelectSingleNode("/root/app/entity/columns").AddAfterSelf(XDocument.Load(Base.GetSettingsPath() + "AppBuilderSetting.xml").SelectSingleNode("/root/category"));
                    xdom.Save(EntitySettingFile);
                }
                #endregion
            }

            Sqlite.Close();      
        }

        private static Dictionary<string, string> GetNotNullColumns(string[] ColName, string[] ColBeNull)
        {
            int Len = ColName.Length;
            Dictionary<string, string> NotNull = new Dictionary<string, string>();
            for (int i = 0; i < Len; i++)
            {
                if (string.IsNullOrEmpty(ColName[i])) continue;
                string Name = ColName[i].ReplaceRegExp("[^0-9a-zA-Z_]", "");
                if (ColBeNull[i] != "1")
                {
                    if (Name == "id") continue;
                    NotNull.Add(Name, "''");
                }
            }
            return NotNull;
        }

        /// <summary>
        /// 从 AppBuilderSetting.xml 中读取表结构配置，转换为sql语句。
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="ColName"></param>
        /// <param name="ColType"></param>
        /// <param name="ColSize"></param>
        /// <param name="ColBeNull"></param>
        /// <param name="ColDefaultValue"></param>
        /// <returns></returns>
        private static string ConvertXmlSettingToSql(string TableName, string[] ColName, string[] ColType, string[] ColSize, string[] ColBeNull, string[] ColDefaultValue)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("CREATE TABLE IF NOT EXISTS ");
            sb.AppendFormat(" {0} ", TableName);
            sb.Append("(");
            int Len = ColName.Length;
            Dictionary<string, string> NotNull = new Dictionary<string, string>();
            for (int i = 0; i < Len; i++)
            {
                if (string.IsNullOrEmpty(ColName[i])) continue;
                string Name = ColName[i].ReplaceRegExp("[^0-9a-zA-Z_]", "");
                sb.AppendFormat("\"{0}\" {1}{2} {3} {4} {5}",
                    Name,
                    ColType[i],
                    string.IsNullOrEmpty(ColSize[i]) ? "" : "(" + ColSize[i] + ")",
                    ColName[i].ToLower() == "id" ? "PRIMARY KEY AUTOINCREMENT" : "",
                    ColBeNull[i] == "1" ? "" : "NOT NULL",
                    string.IsNullOrEmpty(ColDefaultValue[i]) || ColDefaultValue[i] == "{GUID}" ? "" : "DEFAULT " + ColDefaultValue[i].Replace("{", "").Replace("}","")
                    );
                if (i < Len - 1 && !string.IsNullOrEmpty(ColName[i + 1]))
                {
                    sb.Append(",");
                }
                if (ColBeNull[i] != "1")
                {
                    if (Name == "id") continue;
                    NotNull.Add(Name, "''");
                }

            }
            sb.Append(")");
            string Sql = sb.ToString();
            return Sql;
        }

        private static void CreateTableCategory(string App, string Entity)
        {
            
            XDocument xdom = XDocument.Load(Base.GetSettingsPath()+"AppBuilderSetting.xml");
            IEnumerable<XElement> cols = xdom.Element("root").Element("category").Elements("column");
            string[] ColName = cols.Select(n => n.GetAttribute("name")).ToArray();
            string[] ColType = cols.Select(n => n.GetAttribute("type")).ToArray();
            string[] ColSize = cols.Select(n => n.GetAttribute("size")).ToArray();
            string[] ColBeNull = cols.Select(n => n.GetAttribute("null")).ToArray();
            string[] ColDefaultValue = cols.Select(n => n.GetAttribute("default")).ToArray();            
            
            string SqlFile = Base.GetDataBaseFilePath(App);
            AppEntity.Common.DataBase.Sqlite Sqlite = new Common.DataBase.Sqlite(SqlFile);
            string Sql = ConvertXmlSettingToSql("[Entity-" + Entity + "-Category]", ColName, ColType, ColSize, ColBeNull, ColDefaultValue);
            Sqlite.ExecuteSql(Sql);

            //Sqlite.ExecuteSql("CREATE TABLE [Entity-"+Entity+"-Category] ([id] INTEGER PRIMARY KEY NOT NULL, [name] VARCHAR NOT NULL, [detail] TEXT, [status] BOOLEAN NOT NULL DEFAULT(1), [private] BOOLEAN NOT NULL DEFAULT(0), [userid] INTEGER, [sort] INTEGER DEFAULT(0), [parentid] INTEGER, [link] VARCHAR, [linktarget] VARCHAR, [datecreated] DATETIME NOT NULL DEFAULT(datetime ( CURRENT_TIMESTAMP , 'localtime' )), [datemodified] DATETIME)");
            #region 主键 id 从 100000 开始计算
            Sqlite.ExecuteScalar("INSERT INTO [Entity-" + Entity + "-Category] (id,name) VALUES (100000,'');");
            Sqlite.ExecuteSql("DELETE FROM [Entity-" + Entity + "-Category] WHERE id=100000");
            #endregion
            Sqlite.Close();
        }

        /// <summary>
        /// 修改表结构。添加字段、删除字段、修改字段名
        /// </summary>
        /// <param name="App"></param>
        /// <param name="Entity"></param>
        /// <param name="ColName"></param>
        /// <param name="ColType"></param>
        /// <param name="ColSize"></param>
        /// <param name="ColBeNull"></param>
        /// <param name="ColDefaultValue"></param>
        private static void AlterDataTable(string App, string TableName, XElement[] ColumnsOld, string[] ColIDs, string[] ColName, string[] ColType, string[] ColSize, string[] ColBeNull, string[] ColDefaultValue)
        {
            if (ColumnsOld == null || ColumnsOld.Length == 0) return;

            #region 初始化新老表结构
            HashSet<Column> OldColumns = new HashSet<Column>();
            for(int i=0; i<ColumnsOld.Length; i++)
            {
                XElement n = ColumnsOld[i];                
                OldColumns.Add(new Column() { ColID=n.GetAttribute("colid"), Name=n.GetAttribute("name"), Size=n.GetAttribute("size"), Type=n.GetAttribute("type"), BeNull=n.GetAttribute("null"), DefaultValue=n.GetAttribute("default") });
            };
            HashSet<Column> NewColumns = new HashSet<Column>();
            for (int i = 0; i < ColIDs.Length; i++)
            {
                if (string.IsNullOrEmpty(ColName[i])) continue;
                NewColumns.Add(new Column() { ColID=ColIDs[i], Name=ColName[i], Size=ColSize[i], BeNull=ColBeNull[i], Type=ColType[i], DefaultValue=ColDefaultValue[i] });
            }
            #endregion

            #region 查找新字段、修改过的字段、被删除的字段
            var AddColumns = from x in NewColumns where OldColumns.Select(n => n.ColID).Contains(x.ColID) == false && OldColumns.Select(n => n.Name).Contains(x.Name) == false select x;
            var ModifiedColumns = from x in NewColumns from y in OldColumns where x.ColID == y.ColID && (x.Name != y.Name || x.Type!=y.Type || x.DefaultValue!=y.DefaultValue || x.BeNull!=y.BeNull || x.Size!=y.Size) select new{ColID=x.ColID, NewName=x.Name, OldName=y.Name};            
            var RemoveColumns = from x in OldColumns where NewColumns.Select(n => n.ColID).Contains(x.ColID) == false && NewColumns.Select(n => n.Name).Contains(x.Name) == false select x.ColID;
            #endregion

            string SqlFile = Base.GetDataBaseFilePath(App);
            AppEntity.Common.DataBase.Sqlite Sqlite = new Common.DataBase.Sqlite(SqlFile);

            
            #region 处理新插入字段
            if (AddColumns.Count() > 0)
            {

                string OldTableStru = Sqlite.ExecuteScalar("SELECT sql FROM [sqlite_master] WHERE type='table' and tbl_name='" + TableName + "'").ToString();

                Column[] AddList = AddColumns.ToArray();
                StringBuilder sb = new StringBuilder();
                int Len = AddList.Length;
                for (int i = 0; i < Len; i++)
                {
                    Column Col = AddList[i];
                    if (string.IsNullOrEmpty(Col.Name) || Col.Name == "id") continue;
                    string Name = Col.Name.ReplaceRegExp("[^0-9a-zA-Z_]", "");
                    if (OldTableStru.Contains("\"" + Name + "\"")) continue;
                    if (string.IsNullOrEmpty(Name)) continue;
                    sb.AppendFormat("ALTER TABLE [{0}] ADD ", TableName);
                    sb.AppendFormat("\"{0}\" {1}{2} {3} {4} {5}",
                        Name,
                        Col.Type,
                        string.IsNullOrEmpty(Col.Size) ? "" : "(" + Col.Size + ")",
                        "",
                        Col.BeNull == "1" ? "" : "NOT NULL",
                        string.IsNullOrEmpty(ColDefaultValue[i]) || ColDefaultValue[i] == "{GUID}" ? "" : "DEFAULT " + ColDefaultValue[i].Replace("{", "").Replace("}","")
                        );
                    sb.Append(";");
                }
                Sqlite.ExecuteSql(sb.ToString());                
            }
            #endregion
            
            #region 删除字段
            if (RemoveColumns.Count() > 0)
            {                
                StringBuilder sb = new StringBuilder();
                List<string> restCols = new List<string>();
                HashSet<Column> OldAndNewCols = new HashSet<Column>(OldColumns);
                OldAndNewCols.UnionWith(AddColumns);
                foreach (Column Col in OldAndNewCols)
                {
                    if (RemoveColumns.Contains(Col.ColID)) continue;
                    string Name = Col.Name.ReplaceRegExp("[^0-9a-zA-Z_]", "");
                    if (string.IsNullOrEmpty(Name)) continue;
                    if (RemoveColumns.Contains(Col.ColID)) continue;
                    sb.AppendFormat("\"{0}\" {1}{2} {3} {4} {5}",
                        Name,
                        Col.Type,
                        string.IsNullOrEmpty(Col.Size) ? "" : "(" + Col.Size + ")",
                        Name.ToLower() == "id" ? "PRIMARY KEY AUTOINCREMENT" : "",
                        Col.BeNull == "1" ? "" : "NOT NULL",
                        string.IsNullOrEmpty(Col.DefaultValue) || Col.DefaultValue == "{GUID}" ? "" : "DEFAULT " + Col.DefaultValue.Replace("{", "").Replace("}","")
                        );
                    sb.Append(",");
                    restCols.Add(Name);
                }                    
                string TempCols = sb.ToString();
                TempCols = TempCols.Left(TempCols.Length - 1);

                Sqlite.BeginTransaction();
                string OldTableStruct = "CREATE TEMPORARY TABLE [" + TableName + "-Temp] (" + TempCols + ")";
                string NewTableStruct = OldTableStruct.Replace(" TEMPORARY ", " ").Replace("[" + TableName + "-Temp]", "[" + TableName + "]");
                Sqlite.ExecuteSql(OldTableStruct);
                Sqlite.ExecuteSql("INSERT INTO [" + TableName + "-Temp] SELECT " + restCols.ToArray().Join(",") + " FROM [" + TableName + "]");
                Sqlite.ExecuteSql("DROP TABLE [" + TableName + "]");
                Sqlite.ExecuteSql(NewTableStruct);
                Sqlite.ExecuteSql("INSERT INTO [" + TableName + "] SELECT * FROM [" + TableName + "-Temp]");
                Sqlite.ExecuteSql("DROP TABLE [" + TableName + "-Temp]");
                Sqlite.Commit();

            }
            #endregion
            
            #region 处理改名的字段
            if (ModifiedColumns.Count() > 0)
            {                
                Dictionary<string, string> ModifyList = ModifiedColumns.ToDictionary(n => n.OldName, n => n.NewName);
                StringBuilder sb = new StringBuilder();
                foreach (Column Col in NewColumns)
                {
                    if (RemoveColumns.Contains(Col.ColID)) continue;
                    string Name = Col.Name.ReplaceRegExp("[^0-9a-zA-Z_]", "");
                    if (string.IsNullOrEmpty(Name)) continue;
                    sb.AppendFormat("\"{0}\" {1}{2} {3} {4} {5}",
                        Name,
                        Col.Type,
                        string.IsNullOrEmpty(Col.Size) ? "" : "(" + Col.Size + ")",
                        Name.ToLower() == "id" ? "PRIMARY KEY AUTOINCREMENT" : "",
                        Col.BeNull == "1" ? "" : "NOT NULL",
                        string.IsNullOrEmpty(Col.DefaultValue) || Col.DefaultValue == "{GUID}" ? "" : "DEFAULT " + Col.DefaultValue.Replace("{", "").Replace("}","")
                        );
                    sb.Append(",");
                }
                string TempCols = sb.ToString();
                TempCols = TempCols.Left(TempCols.Length - 1);
                string NewTempTableStru = "CREATE TEMPORARY TABLE [" + TableName + "-Temp] (" + TempCols + ")";
                string NewTableStru = NewTempTableStru.Replace(" TEMPORARY ", " ").Replace("[" + TableName + "-Temp]", "[" + TableName + "]");

                StringBuilder sb2 = new StringBuilder();
                foreach (Column oldcol in OldColumns)
                {
                    if (RemoveColumns.Contains(oldcol.ColID)) continue;
                    sb2.AppendFormat("[{0}]{1},", oldcol.Name, ModifyList.ContainsKey(oldcol.Name) ? " as [" + ModifyList[oldcol.Name] + "]" : "");
                }
                foreach (Column newcol in AddColumns)
                {
                    if (RemoveColumns.Contains(newcol.ColID)) continue;
                    sb2.AppendFormat("[{0}],", newcol.Name);
                }
                string NewCols = sb2.ToString();
                NewCols = NewCols.Left(NewCols.Length - 1);


                #region 原来允许为空的字段，修改为 Not null，必须修改现存数据这些字段为空字符串
                var NotNullCols = from c in ColumnsOld 
                                  from d in NewColumns
                                  let cid = c.GetAttribute("colid")                                  
                                  let n = c.GetAttribute("null")
                                  where n == "1" && d.ColID == cid && d.BeNull == "0"
                                  select c.GetAttribute("name");
                Sqlite.BeginTransaction();
                foreach (string Col in NotNullCols)
                {
                    Sqlite.ExecuteSql("UPDATE [" + TableName + "] SET " + Col + "='' WHERE " + Col + " IS NULL;");    
                }
                Sqlite.Commit();                
                #endregion                
                Sqlite.BeginTransaction();
                Sqlite.ExecuteSql(NewTempTableStru);
                Sqlite.ExecuteSql("INSERT INTO [" + TableName + "-Temp] SELECT " + NewCols + " FROM [" + TableName + "]");
                Sqlite.ExecuteSql("DROP TABLE [" + TableName + "]");
                Sqlite.ExecuteSql(NewTableStru);
                Sqlite.ExecuteSql("INSERT INTO [" + TableName + "] SELECT * FROM [" + TableName + "-Temp]");
                Sqlite.ExecuteSql("DROP TABLE [" + TableName + "-Temp]");
                Sqlite.Commit();

            }
            #endregion
           
            Sqlite.Close();
        }

        private static XDocument GetEmptyEntitySettings(string AppName, string EntityName)
        {
            XDocument xdom = XDocument.Load( new StringReader("<?xml version='1.0' encoding='utf-8'?><root><app name='" + AppName + "'><entity name='" + EntityName + "'></entity></app></root>"));
            XElement Entity = xdom.Element("root").Element("app").Element("entity");
            Entity.SetAttribute("name", "");
            Entity.SetAttribute("title", "");
            Entity.SetAttribute("defaultcat", "0");
            Entity.SetAttribute("zh-cn", "");
            Entity.SetAttribute("en-us", "");
            Entity.Add(new XElement("description"));
            Entity.Add(new XElement("columns"));
            Entity.Add(new XElement("indexs"));
            Entity.Add(new XElement("pages"));
            Entity.Add(new XElement("datecreated"));
            Entity.Add(new XElement("datemodified"));
            return xdom;
        }
    }
}